package db;

import java.sql.*;
import javax.naming.*;
import javax.sql.*;

public class DatabaseConnector {
    private Context initContext;
    private DataSource ds;

    private Connection conn;
    private PreparedStatement pstmt;
    private ResultSet rs;

    public DatabaseConnector(String DatabaseName){
        try{
            initContext = new InitialContext();
 
            ds = (DataSource)initContext.lookup("java:/comp/env/jdbc/wmmd_db");
            if (ds == null ) 
            {
                throw new Exception("No DataSource Found!  Check context.xml file.");
            }
            else
            {
                conn = getConnection();
            }

        } catch (Exception e){
            System.out.println(e.getClass() + ", " + e.getMessage());
        }
    }

    public Connection getConnection() throws SQLException{
        if (ds != null){
            conn =  ds.getConnection();
        } else{
            throw new NullPointerException("null DataSource");
        }
       return conn;
    }
   
    private String getSqlList(String[] fields) throws Exception{
        StringBuffer sb = new StringBuffer();
        for(int i = 0; i < fields.length - 1; i++ ){
            sb.append(fields[i]);
            sb.append(", ");
        }
        sb.append(fields[fields.length - 1]);
        return sb.toString();
    }

    public int insertSQL(String tableName, String[] fieldNames, Object[] fieldValues){
        int succ;

        String[] paras = new String[fieldNames.length];
        for (int i = 0; i < fieldNames.length; i++) {
            paras[i] = "?"; }

        try{
            String SQLstr = "INSERT INTO " + tableName + " (" + getSqlList(fieldNames) + ") VALUES (" + getSqlList(paras) + ")";
            pstmt = conn.prepareStatement(SQLstr);

            for (int i = 0; i < fieldNames.length; i++) {
                pstmt.setObject(i + 1, fieldValues[i]);  }

            succ = pstmt.executeUpdate();
            pstmt.close();
        } catch(Exception e){
            System.out.println(e.getClass() + ": " + e.getMessage());
            return PreparedStatement.EXECUTE_FAILED;
        } finally {
            try {
                if(pstmt != null) pstmt.close();
            } catch (Exception e)
              {System.out.println(e.getMessage());}
        }
        return succ;
    }

    public Object[] selectSQL(String tableName, String[] fieldNames, String idFieldName, Object idValue)
    {
        Object[] result = new Object[fieldNames.length];

	try {
            String SQLstr = "SELECT " + getSqlList(fieldNames) + " FROM " + tableName + " WHERE " + idFieldName + " = ?";

	    pstmt = conn.prepareStatement(SQLstr);
            pstmt.setObject(1, idValue);

            rs = pstmt.executeQuery();
	    if (rs.next()) {
                for ( int i = 0; i < fieldNames.length; i++) {
                    result[i] = rs.getObject(i+1);
                }
	        rs.close();
	        pstmt.close();
	    }
	}
	catch(Exception e){
            System.out.println(e.getClass() + ": " + e.getMessage());
	    return null;
        } 
        finally
        {
            try
            {
                if(rs != null) rs.close();
                if(pstmt != null) pstmt.close();
            }
            catch (Exception e)
                {System.out.println(e.getMessage());}
	    return result;
        }
    }

    //returns multiple rows with a WHERE statement
    public String[][] selectWhereSQL(String tableName, String[] fieldNames, String idFieldName, Object idValue) {
        String[][] result;

        try {
            String SQLstr = "SELECT " + getSqlList(fieldNames) + " FROM " + tableName + " WHERE " + idFieldName + " = ?";
            
            pstmt = conn.prepareStatement("SELECT count(*) FROM ("+SQLstr+") A");
            pstmt.setObject(1, idValue);
            rs = pstmt.executeQuery();
            rs.next();
            int len = rs.getInt(1);

            pstmt = conn.prepareStatement(SQLstr);
            pstmt.setObject(1, idValue);
            rs = pstmt.executeQuery();
            //if (rs.next()) {
            if(rs != null) {
                result = new String[len][fieldNames.length];
                for (int j=0;rs.next();j++){
                    for ( int i = 0; i < fieldNames.length; i++) {
                        result[j][i] = rs.getString(i+1);
                    }
                }
                rs.close();
                pstmt.close();
            } else return null;
        }
        catch(Exception e) {
            System.out.println(e.getClass() + ": " + e.getMessage());
            return null;
        } finally {
            try {
                if(rs != null) rs.close();
                if(pstmt != null) pstmt.close();
                    } catch (Exception e)
                      {System.out.println(e.getMessage());}
        }
        return result;
    }
    
    //get MAX value
     public String selectSQL(String tableName, String maxValue, String asValue) {
        String id;

        try{
            String SQLstr = "SELECT MAX(" + maxValue + ") as \"" + asValue + "\" FROM " + tableName;

            pstmt = conn.prepareStatement(SQLstr);
            rs = pstmt.executeQuery();
            if(rs.next())
            {
            id = rs.getString("MaxID");
            rs.close();
            pstmt.close();

            } else return null;
        }
        catch(Exception e) {
            System.out.println(e.getClass() + ": " + e.getMessage());
            return null;
        } finally {
            try {
                if(rs != null) rs.close();
                if(pstmt != null) pstmt.close();
                    } catch (Exception e)
                      {System.out.println(e.getMessage());}
        }
        return id;
	}

     //get MAX value with a Where
     public int selectSQL(String tableName, String maxValue, String asValue, String idFieldName, String idValue) {
        int id;
        
        try{
            String SQLstr = "SELECT MAX(" + maxValue + ") as \"" + asValue + "\" FROM " + tableName + " WHERE " + idFieldName + " = '" + idValue + "'";

            pstmt = conn.prepareStatement(SQLstr);
            rs = pstmt.executeQuery();
            rs.next();
            // Add 1 to the user's last entry ID
            id = rs.getInt("MaxID") + 1;
            rs.close();
            pstmt.close();
        }
        catch (Exception e) {
            System.out.println(e.getClass() + ": " + e.getMessage());
            return PreparedStatement.EXECUTE_FAILED;
        } finally {
            try {
                if(rs != null) rs.close();
                if(pstmt != null) pstmt.close();
                    } catch (Exception e)
                      {System.out.println(e.getMessage());}
        }
        return id;
	}

    //grabs records without explicit WHERE statements
    public String[][] selectSQL(String tableName, String[] fieldNames) {
        String[][] result;

        try{
            String SQLstr = "SELECT " + getSqlList(fieldNames) + " FROM " + tableName;
            pstmt = conn.prepareStatement("SELECT count(*) FROM ("+SQLstr+") A");
            rs = pstmt.executeQuery();
            rs.next();
            int len = rs.getInt(1);

            pstmt = conn.prepareStatement(SQLstr);
            rs = pstmt.executeQuery();
            if(rs != null) {
		result = new String[len][fieldNames.length];
                for (int j=0;rs.next();j++){
                    for ( int i = 0; i < fieldNames.length; i++) {
                        result[j][i] = rs.getString(i+1);
                    }
		}

                rs.close();
                pstmt.close();
            } else return null;
        }
        catch(Exception e) {
            System.out.println(e.getClass() + ": " + e.getMessage());
            return null;
        } finally {
            try {
                if(rs != null) rs.close();
                if(pstmt != null) pstmt.close();
                    } catch (Exception e)
                      {System.out.println(e.getMessage());}
          }
        return result;
    }

    public int updateSQL(String tableName, String[] fieldNames, Object[] fieldValues, String idFieldName, Object idValue){
        int succ;

        String[] paras = new String[fieldNames.length];
        for (int i = 0; i < fieldNames.length; i++) {
            paras[i] = fieldNames[i] + "=?";  }
        try {
            int j;
            String SQLstr = "UPDATE " + tableName + " SET " + getSqlList(paras) + " WHERE " + idFieldName + " = ?";

            pstmt = conn.prepareStatement(SQLstr);
            for (j = 0; j < fieldNames.length; j++) {
                pstmt.setObject(j + 1, fieldValues[j]);
            }
            pstmt.setObject(j + 1, idValue);
            succ = pstmt.executeUpdate();
            pstmt.close();
        }
        catch (Exception e) {
            System.out.println(e.getClass() + ": " + e.getMessage());
            return PreparedStatement.EXECUTE_FAILED;
        } finally {
            try {
                if(pstmt != null) pstmt.close();
                    } catch (Exception e)
                      {System.out.println(e.getMessage());}
          }
        return succ;
    }

    public int deleteSQL(String tableName, String idFieldName, Object idValue) {
        int succ;

        try {
            String SQLstr = "DELETE FROM " + tableName + " WHERE " + idFieldName + " = ?";
            pstmt = conn.prepareStatement(SQLstr);
            pstmt.setObject(1, idValue);
            succ = pstmt.executeUpdate();
            pstmt.close();
        } catch(Exception e){
            System.out.println(e.getClass() + ": " + e.getMessage());
            return PreparedStatement.EXECUTE_FAILED;
        } finally {
            try {
                if(pstmt != null) pstmt.close();
                    } catch (Exception e)
                      {System.out.println(e.getMessage());}
          }
        return succ;
    }

    // Truncate multiple tables
    public int truncateSQL(String[] tableNames) {
        int succ = PreparedStatement.EXECUTE_FAILED;

        try {
            for (int i = 0; i < tableNames.length; i++) {
                String SQLstr= "TRUNCATE TABLE ?";
                pstmt = conn.prepareStatement(SQLstr);
                pstmt.setString(1, tableNames[i]);
                succ = pstmt.executeUpdate();
                pstmt.close();
            }
        }
        catch (Exception e) {
            System.out.println(e.getClass() + ": " + e.getMessage());
            return PreparedStatement.EXECUTE_FAILED;
        } finally {
            try {
                if(pstmt != null) pstmt.close();
                    } catch (Exception e)
                      {System.out.println(e.getMessage());}
          }
        return succ;
    }

    public static String getDatabaseType()
        {return "";}

    public String[][] selectSQL(String SQLstr, int fieldLen) {
        String[][] result;

        try {
            pstmt = conn.prepareStatement("SELECT count(*) FROM ("+SQLstr+") A");
            rs = pstmt.executeQuery();
            rs.next();
            int len = rs.getInt(1);

            pstmt = conn.prepareStatement(SQLstr);
            rs = pstmt.executeQuery();
            if (rs != null){
                result = new String[len][fieldLen];
                for (int j=0;rs.next();j++){
                    for ( int i = 0; i < fieldLen;i++) {
                        result[j][i] = rs.getString(i+1);
                    }
                }
                rs.close();
                pstmt.close();
            } else return null;
        } catch(Exception e) {
            System.out.println(e.getClass() + ": " + e.getMessage());
            return null;
        } finally {
            try {
                if(rs != null) rs.close();
                if(pstmt != null) pstmt.close();
                    } catch (Exception e)
                      {System.out.println(e.getMessage());}
          }
        return result;
    }

    //handles DELETE, UPDATE
    public int freeformSQL(String SQL, Object[] fields){
        int succ;

         try {
            pstmt = conn.prepareStatement(SQL);
            for (int j = 0; j < fields.length; j++) {
                pstmt.setObject(j + 1, fields[j]);
            }
            succ = pstmt.executeUpdate();
            pstmt.close();
        }
        catch (Exception e) {
            System.out.println(e.getClass() + ": " + e.getMessage());
            return PreparedStatement.EXECUTE_FAILED;
        } finally {
            try {
                if(pstmt != null) pstmt.close();
                    } catch (Exception e)
                      {System.out.println(e.getMessage());}
          }
        return succ;
    }

    //freeform; processes SELECT queries with additional data (like inner queries) with WHERE statements
    //it is assumed the ? is already provided
    public String[][] selectSQL(String SQLstr, int fieldLen, Object[] idValues) {
        String[][] result;

        try {
            pstmt = conn.prepareStatement("SELECT count(*) FROM ("+SQLstr+") A");
            for (int i=0;i<idValues.length;i++){
		pstmt.setObject(i+1, idValues[i]); }
            rs = pstmt.executeQuery();
            rs.next();
            int len = rs.getInt(1);

            pstmt = conn.prepareStatement(SQLstr);
            for (int i=0;i<idValues.length;i++){
		pstmt.setObject(i+1, idValues[i]); }
            rs = pstmt.executeQuery();
            if(rs != null)
            {
		result = new String[len][fieldLen];
		for (int j=0;rs.next();j++){
                    for ( int i = 0; i < fieldLen;i++) {
			result[j][i] = rs.getString(i+1);
                    }
		}
                rs.close();
                pstmt.close();
            } else return null;

        } catch(Exception e) {
            System.out.println(e.getClass() + ": " + e.getMessage());
            return null;
        } finally {
            try {
                if(rs != null) rs.close();
                if(pstmt != null) pstmt.close();
                    } catch (Exception e)
                      {System.out.println(e.getMessage());}
          }
        return result;
    }

     public ResultSet selectSQL_RS(String SQLstr, Object[] idValues) {
        try {
            pstmt = conn.prepareStatement(SQLstr);

            if(idValues != null)
                for (int i=0;i<idValues.length;i++){
                    pstmt.setObject(i+1, idValues[i]); }
            rs = pstmt.executeQuery();

            return rs;

        } catch(Exception e) {
            System.out.println(e.getClass() + ": " + e.getMessage());
            return null;
        }
     }

     public ResultSet selectSQL_RS(String SQLstr)
     {
        try
        {
            pstmt = conn.prepareStatement(SQLstr);
            rs = pstmt.executeQuery();
            return rs;

        } catch(Exception e) {
            System.out.println(e.getClass() + ": " + e.getMessage());
            return null;
        }
    }

    public void CleanUp()
    {
        try
        {
            if(rs != null) rs.close();
            if(pstmt != null) pstmt.close();
            if(conn != null) conn.close();
        }
        catch(Exception e) {
            System.out.println(e.getClass() + ": " + e.getMessage());
        }
    }
}